/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import Entity.Dosen;
import Entity.PenawaranPpm;
import Entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import library.Koneksi;

/**
 *
 * @author user
 * model untuk menangani operasi database berkaitan dengan penawaran ppm
 */
public class DaftarPenawaranPpm {
    
    PenawaranPpm penawaranPpm;
    
    //method digunakan untuk mengambil daftar penawaran ppm
    public List<PenawaranPpm> ambilDaftarPenawaranPpm(){
        List<PenawaranPpm> daftarPenawaranPpm = new ArrayList<PenawaranPpm>();
        
        String sql = "SELECT * FROM penawaran_ppm, dosen, user where penawaran_ppm.ID_USER=dosen.ID_USER AND dosen.ID_USER = user.ID_USER order by penawaran_ppm.BATAS_PENDAFTARAN DESC";
                
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                this.penawaranPpm = new PenawaranPpm();
                penawaranPpm.setIdPenawaran(rs.getInt("ID_PENAWARAN"));
                penawaranPpm.setTopik(rs.getString("TOPIK"));
                penawaranPpm.setKeteranganPenawaran(rs.getString("KETERANGAN_PENAWARAN"));
                penawaranPpm.setBatasPendaftaran(rs.getString("BATAS_PENDAFTARAN"));
                penawaranPpm.setFileKeteranganPenawaran(rs.getString("FILE_KETERANGAN_PENAWARAN"));
                penawaranPpm.setSumberInformasi(rs.getString("SUMBER_INFORMASI"));
                
                User user = new User();
                user.setIdUser(rs.getString("ID_USER"));
                user.setNamaUser(rs.getString("NAMA_USER"));
                
                Dosen dosen = new Dosen();
                dosen.setUser(user);
                
                this.penawaranPpm.setDosen(dosen);
                
                daftarPenawaranPpm.add(this.penawaranPpm);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarPenawaranPpm;
    }
    
    //method digunakan untuk menambah data penawaran ppm
    public boolean tambahPenawaranPpm(PenawaranPpm penawaranPpm) {

        boolean hasil = false;
        this.penawaranPpm = penawaranPpm;
        
        Connection conn = Koneksi.getKoneksi();
        PreparedStatement st = null;        
        
        String sql = "insert into penawaran_ppm(TOPIK, KETERANGAN_PENAWARAN, BATAS_PENDAFTARAN, FILE_KETERANGAN_PENAWARAN, ID_USER, SUMBER_INFORMASI) "
                + "value('" + this.penawaranPpm.getTopik()
                +"', '" + this.penawaranPpm.getKeteranganPenawaran()
                +"', '" + this.penawaranPpm.getBatasPendaftaran()
                +"', '" + this.penawaranPpm.getFileKeteranganPenawaran()
                + "', '" + this.penawaranPpm.getDosen().getUser().getIdUser()
                +"', '"+this.penawaranPpm.getSumberInformasi()+"')";

        System.out.println(sql);
        
        try {
            
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return hasil;

    }
    
    //method digunakan untuk menambah data dosen yang ikut berpartisipasi dalam penawaran ppm
    public boolean tambahPendaftarPenawaranPpm(PenawaranPpm penawaranPpm, Dosen dosen) {

        boolean hasil = false;
        this.penawaranPpm = penawaranPpm;
        
        Connection conn = Koneksi.getKoneksi();
        PreparedStatement st = null;        
        
        String sql = "insert into pendaftar_penawaran_ppm(ID_PENAWARAN, ID_USER) "
                + "value(" + this.penawaranPpm.getIdPenawaran()
                +", '"+dosen.getUser().getIdUser()+"')";

        System.out.println(sql);
        
        try {
            
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return hasil;

    }
    
    //method digunakan untuk menghapus data dosen yang batal berpartisipasi dalam penawaran ppm
    public boolean kurangiPendaftarPenawaranPpm(PenawaranPpm penawaranPpm, Dosen dosen) {

        boolean hasil = true;

        PreparedStatement st = null;

        String sql = "DELETE FROM pendaftar_penawaran_ppm WHERE ID_PENAWARAN = " +penawaranPpm.getIdPenawaran()+" and ID_USER = '"+dosen.getUser().getIdUser()+"'";

        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }

    //method digunakan untuk mengubah data penawaran ppm
    public boolean ubahPenawaranPpm(PenawaranPpm penawaranPpm) {

        boolean hasil = false;
        this.penawaranPpm = penawaranPpm;

        String sql = "update penawaran_ppm set TOPIK = '" + this.penawaranPpm.getTopik() + 
                "', KETERANGAN_PENAWARAN = '" + this.penawaranPpm.getKeteranganPenawaran() +
                "', BATAS_PENDAFTARAN = '" + this.penawaranPpm.getBatasPendaftaran() +
                "', FILE_KETERANGAN_PENAWARAN = '" + this.penawaranPpm.getFileKeteranganPenawaran()+
                "', ID_USER = '" + this.penawaranPpm.getDosen().getUser().getIdUser()+
                "', SUMBER_INFORMASI = '" + this.penawaranPpm.getSumberInformasi()
                + "' where ID_PENAWARAN = " + this.penawaranPpm.getIdPenawaran();

        System.out.println(sql);

        PreparedStatement st = null;
        try {
            Connection conn = Koneksi.getKoneksi();
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return hasil;

    }

    //method digunakan untuk menghapus data penawaran ppm
    public boolean hapusPenawaranPpm(PenawaranPpm penawaranPpm) {

        boolean hasil = true;

        PreparedStatement st = null;

        String sql = "DELETE FROM penawaran_ppm WHERE ID_PENAWARAN = " +penawaranPpm.getIdPenawaran();

        Connection conn = Koneksi.getKoneksi();
        try {
            st = conn.prepareStatement(sql);
            int rs = st.executeUpdate(sql);
            if (rs > 0) {
                hasil = true;
            } else {
                hasil = false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return hasil;

    }
    
    //method digunakan untuk mengambil detail data penawaran ppm
    public PenawaranPpm detailPenawaranPpm(PenawaranPpm penawaranPpm) {

        String sql = "SELECT * FROM penawaran_ppm, dosen, user "
                + "where penawaran_ppm.ID_USER=dosen.ID_USER AND dosen.ID_USER = user.ID_USER "
                + "AND penawaran_ppm.ID_PENAWARAN=" + penawaranPpm.getIdPenawaran();
        PreparedStatement st = null;
        Connection conn;
        ResultSet rs = null;
        try {

            conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while (rs.next()) {
                this.penawaranPpm = new PenawaranPpm();
                this.penawaranPpm.setIdPenawaran(rs.getInt("ID_PENAWARAN"));
                this.penawaranPpm.setTopik(rs.getString("TOPIK"));
                this.penawaranPpm.setKeteranganPenawaran(rs.getString("KETERANGAN_PENAWARAN"));
                this.penawaranPpm.setBatasPendaftaran(rs.getString("BATAS_PENDAFTARAN"));
                this.penawaranPpm.setFileKeteranganPenawaran(rs.getString("FILE_KETERANGAN_PENAWARAN"));
                this.penawaranPpm.setSumberInformasi(rs.getString("SUMBER_INFORMASI"));
                
                User user = new User();
                user.setIdUser(rs.getString("ID_USER"));
                user.setNamaUser(rs.getString("NAMA_USER"));
                
                Dosen dosen = new Dosen();
                dosen.setUser(user);
                
                this.penawaranPpm.setDosen(dosen);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return this.penawaranPpm;

    }
    
    //method digunakan untuk mengambil daftar data dosen yang ikut berpartisipasi dalam kegiatan penawaran ppm
    public List<Dosen> daftarPendaftarPenawaranPpm(PenawaranPpm penawaranPpm){
        List<Dosen> daftarDosen = new ArrayList<Dosen>();
        
        String sql = "SELECT user.ID_USER, user.NAMA_USER FROM penawaran_ppm, dosen, user, pendaftar_penawaran_ppm "
                + "where pendaftar_penawaran_ppm.ID_USER=dosen.ID_USER AND dosen.ID_USER = user.ID_USER "
                + "AND penawaran_ppm.ID_PENAWARAN = pendaftar_penawaran_ppm.ID_PENAWARAN "
                + "AND penawaran_ppm.ID_PENAWARAN=" + penawaranPpm.getIdPenawaran();
                
        PreparedStatement st = null;
        ResultSet rs = null;
        try {

            Connection conn = Koneksi.getKoneksi();

            st = conn.prepareStatement(sql);
            rs = st.executeQuery();

            while(rs.next()){
                                
                User user = new User();
                user.setIdUser(rs.getString("ID_USER"));
                user.setNamaUser(rs.getString("NAMA_USER"));
                
                Dosen dosen = new Dosen();
                dosen.setUser(user);
                
                daftarDosen.add(dosen);
            }
        } catch (SQLException ex) {
            Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
            try {
                rs.close();
            } catch (SQLException ex) {
                Logger.getLogger(DaftarPenawaranPpm.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return daftarDosen;
    }
    
}
